﻿--创建数据库
create database Compang
go
use Compang
go

--创建部门信息表
create table SectionInfo
(
	SectionID  int primary key identity,--部门编号，标识列，主键
	SectionName  varchar(10) not null  --部门名称
)

----创建员工信息表
create table UserInfo
(
	UserNo  int primary key identity(1,1) not null,--员工编号
	UserName  varchar(10) unique not null,--员工姓名
	UserSex   varchar(2) not null check (UserSex='男' or UserSex='女'),--员工性别
	UserAge   int check(UserAge>=1 and UserAge<=100) not null,--员工年龄
	UserCity  varchar(50) default('福建省龙岩市'),--所在城市
	UserSection  int foreign key (UserSection ) references SectionInfo (SectionID ),--员工部门 
	UserSalary decimal(5,2) default('0') --员工月薪  
)
--创建员工考勤表
create table WorkInfo
(
	 WorkId int primary key identity not null,--考勤编号
	 UserId  int foreign key (UserId) references UserInfo (UserNo),--考勤员工
	 WorkTime datetime not null,--考勤时间 
	 WorkDescription  varchar(40) check(WorkDescription='迟到' or WorkDescription='早退' or
										WorkDescription='旷工' or WorkDescription='病假' or
										WorkDescription='事假' or WorkDescription='出勤' ),--考勤说明

)

--为部门信息表添加5条记录
	insert into SectionInfo (SectionName)
	values ('人事部')
	insert into SectionInfo (SectionName)
	values ('运营部')
	insert into SectionInfo (SectionName)
	values ('开发部')
	insert into SectionInfo (SectionName)
	values ('市场部')
	insert into SectionInfo (SectionName)
	values ('售后部')
	
	select * from SectionInfo

--为员工信息表添加20条记录
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('赵德怀','男',25,'福建省漳州市',1,'500')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('李建明','男',24,'福建省漳州市',1,'900')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('刘桂芳','女',26,'福建省龙岩市',2,'950')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('张宇杰','男',30,'福建省漳州市',3,'999')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('王振东','男',25,'福建省南平市',3,'999')

	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('赵金泰','男',24,'福建省三明市',3,'960')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('李琦君','男',26,'福建省南平市',3,'900')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('赵旻哲','男',26,'福建省福州市',1,'800')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('孙予超','男',31,'福建省福州市',2,'800')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('张楷翰','男',28,'福建省漳州市',2,'850')

	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('钱佳文','女',29,'福建省厦门市',4,'950')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('柳时穆','女',26,'福建省厦门市',4,'950')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('张连清','女',23,'福建省漳州市',4,'950')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('穆雨烟','女',24,'福建省漳州市',5,'950')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('邱琴','女',23,'福建省漳州市',5,'850')

	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('李落含','女',29,'福建省漳州市',5,'850')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('马平荣','男',28,'福建省泉州市',2,'850')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('金凡齐','男',26,'福建省漳州市',2,'850')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('齐飞','男',23,'福建省泉州市',1,'850')
	insert into UserInfo(UserName,UserSex,UserAge,UserCity,UserSection,UserSalary)
	values('齐铭','男',26,'福建省漳州市',1,'850')

	select * from UserInfo

--为员工考勤表添加20条记录
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(1,'2020-7-1','出勤')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(2,'2020-7-1','出勤')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(3,'2020-7-1','出勤')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(4,'2020-7-1','出勤')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(5,'2020-7-1','病假')

	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(6,'2020-7-1','病假')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(7,'2020-7-1','迟到')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(8,'2020-7-1','迟到')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(9,'2020-7-1','迟到')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(10,'2020-7-1','迟到')

	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(11,'2020-7-1','事假')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(12,'2020-7-1','事假')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(13,'2020-7-1','事假')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(14,'2020-7-1','早退')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(15,'2020-7-1','早退')

	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(16,'2020-7-1','旷工')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(17,'2020-7-1','旷工')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(18,'2020-7-1','旷工')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(19,'2020-7-1','旷工')
	insert into WorkInfo(UserId,WorkTime, WorkDescription)
	values(20,'2020-7-1','旷工')

	select * from WorkInfo

--1.查询公司的部门数量
	select count(*) 部门数量 from SectionInfo

--2.查询公司的员工数量
	select count(*) 员工数量 from UserInfo

--3.查询所有部门的员工数量和工资平均值
	select  count(*) 员工数量, avg(UserSalary) 平均工资 from UserInfo

--4.查询每个年龄的男女生人数
	select distinct UserAge 年龄, count(*) 男女生人数 from UserInfo
	group by UserAge

--5.查询所有部门员工的平均薪资
	select  avg(UserSalary)平均工资 from UserInfo

--6.查询员工最高工资和最低工资的差距
	select max(UserSalary) - min(UserSalary) 工资差距 from UserInfo 

--7.查询平均工资高于 8000 的部门 id 和它的平均工资.
	select UserSection 部门id, avg(UserSalary)平均工资 from UserInfo 
	group by UserSection 
	having avg(UserSalary)>8000
  
--8.查询公司员工工资的最大值，最小值，平均值，总和
	select max(UserSalary) 最高工资,min(UserSalary)最低工资,avg(UserSalary)平均工资,sum(UserSalary)工资总和 from UserInfo 
	
--9.(有员工的城市)各个城市的平均工资
	select distinct UserCity 城市, avg(UserSalary)平均工资 from UserInfo 
	group by UserCity

--10.查询每个员工本月的出勤情况信息
	select UserId 考勤员工ID,(case when WorkDescription='出勤' then 1 else 0 end) 出勤次数,
							 (case when WorkDescription='迟到' then 1 else 0 end) 迟到次数,
							 (case when WorkDescription='早退' then 1 else 0 end) 早退次数,
							 (case when WorkDescription='旷工' then 1 else 0 end) 旷工次数,
							 (case when WorkDescription='病假' then 1 else 0 end) 病假次数,
							 (case when WorkDescription='事假' then 1 else 0 end) 事假次数
	 
	from WorkInfo
	group by UserId,WorkDescription




